package cn.dao;


import cn.entity.Staff;
import cn.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StaffDaoImpl implements StaffDao {
    @Override
    public List<Staff> findALL() {
        List<Staff> staff = new ArrayList<Staff>();
        Connection con = DBUtil.getCon();
        String sql = "select id, name, job, salary from staff";
        Statement stmt = null; //定义用于执行SQL语句
        ResultSet rs = null; //存储结果集
        try {
            stmt = con.createStatement();
            rs = stmt.executeQuery(sql);
            // 处理结果集
            while (rs.next()) {
                int id = rs.getInt("ID");
                String name = rs.getString("NAME");
                String job = rs.getString("JOB");
                int salary = rs.getInt("SALARY");

                Staff staffs = new Staff(id, name, job, salary);
                staff.add(staffs);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.closeCon(con);
        }
        return staff;
    }

    @Override
    public boolean addData(Staff staff) {
        boolean flag = true; //默认保存成功
        Connection con = DBUtil.getCon();
        PreparedStatement pst = null;
        String sql = "insert into staff(name,job,salary) values(?,?,?)";
        try {
            pst = con.prepareStatement(sql);
            pst.setString(1, staff.getName());
            pst.setString(2, staff.getJob());
            pst.setInt(3, staff.getSalary());
            pst.executeUpdate();
        } catch (SQLException e) {
            flag = false;
            throw new RuntimeException(e);
        } finally {
            DBUtil.closeCon(con);
        }
        return flag;
    }

    @Override
    public void deleteId(int id) {
        Connection con = DBUtil.getCon();
        PreparedStatement pst = null;
        String sql = "delete from staff where id = ?;";

        try {
            pst = con.prepareStatement(sql);
            pst.setInt(1, id);
            pst.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException();
        } finally {
            DBUtil.closeCon(con);
        }
    }

    @Override
    public void updateId(Staff staff) {
        Connection con = DBUtil.getCon();
        PreparedStatement pst = null;
        String sql = "update staff SET name=?, job=?, salary=? where id=?;";

        try {
            pst = con.prepareStatement(sql);
            pst.setString(1, staff.getName());
            pst.setString(2, staff.getJob());
            pst.setInt(3, staff.getSalary());
            pst.setInt(4, staff.getId());
            pst.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException();
        } finally {
            DBUtil.closeCon(con);
        }
    }

    @Override
    public List<Staff> searchName(String keyword) {
        List<Staff> staffList = new ArrayList<>();
        Connection con = DBUtil.getCon();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            String query = "SELECT * FROM staff WHERE name LIKE ?";//模糊查询
            preparedStatement = con.prepareStatement(query);
            preparedStatement.setString(1, "%" + keyword + "%");
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                //获取数据创建Staff对象(列名为id name job salary)
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String job = resultSet.getString("job");
                int salary = resultSet.getInt("salary");
                //new emp对象
                Staff staff = new Staff(id, name, job, salary);
                staffList.add(staff);
            }
        } catch (SQLException e) {
            throw new RuntimeException();
        } finally {
            //释放资源
            DBUtil.closeCon(con);
        }
        return staffList;
    }


    @Override
    public Staff findID(int id) {
        Connection con = DBUtil.getCon();
        PreparedStatement pst = null;
        ResultSet rs = null;
        Staff staff = new Staff();
        String sql = "select * from staff where id = ?;";
        try {
            pst = con.prepareStatement(sql);
            pst.setInt(1, id);
            rs = pst.executeQuery();
            while (rs.next()) {
                String name = rs.getString("NAME");
                String job = rs.getString("JOB");
                int salary = rs.getInt("SALARY");

                staff = new Staff(id, name, job, salary);
            }
        } catch (SQLException e) {
            throw new RuntimeException();
        } finally {
            DBUtil.closeCon(con);
        }

        return staff;
    }
}
